package com.itany.corejava.code13_jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Miss Chen
 * @version 1.0
 * @date 2024年08月08日8:55
 */
public class Test08_练习 {

    public static void main(String[] args) throws DataAccessException {
        // Student student=new Student("23093","lili",22);
        // addStudent(student);

        // deleteByNo("23091");

        //
        // Student student=new Student("23092","huahua",24);
        // updateByNo(student);

        // List<Student> students=selectAll();
        // System.out.println(students);

        // Student stu=selectByNo("23093");
        // System.out.println(stu);

        // List<Student> students= selectByName("ua");
        // System.out.println(students);

        List<Student> students=selectByAge(22,25);
        System.out.println(students);
    }

    /**
     * 添加学生信息
     * @param student
     * @throws DataAccessException
     */
    public static void addStudent(Student student) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student ")
                    .append(" (no,name,age) ")
                    .append(" values ")
                    .append(" (?,?,?) ")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setString(1,student.getNo());
            ps.setString(2,student.getName());
            ps.setInt(3,student.getAge());


            //执行DML操作
            int rows=ps.executeUpdate();

            System.out.println("添加成功");
        } catch (DataAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(conn,ps,null);
        }
    }

    /**
     * 根据学生学号来删除学生信息
     * @param no
     * @throws DataAccessException
     */
    public static void deleteByNo(String no) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append(" delete from ")
                    .append(" t_student ")
                    .append(" where no=? ")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setString(1,no);


            //执行DML操作
            int rows=ps.executeUpdate();

            System.out.println("删除成功");
        } catch (DataAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(conn,ps,null);
        }
    }

    /**
     * 根据学号修改指定学生信息
     * @param student
     * @throws DataAccessException
     */
    public static void updateByNo(Student student) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append(" update  ")
                    .append(" t_student ")
                    .append(" set  ")
                    .append(" name=?,")
                    .append(" age=?  ")
                    .append(" where no=? ")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setString(1,student.getName());
            ps.setInt(2,student.getAge());
            ps.setString(3,student.getNo());


            //执行DML操作
            int rows=ps.executeUpdate();

            System.out.println("修改成功");
        } catch (DataAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(conn,ps,null);
        }
    }

    /**
     * 查询所有学生信息
     * @return
     * @throws DataAccessException
     */
    public static List<Student> selectAll() throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        List<Student> students=new ArrayList<>();
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student ")
                    .toString();
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                Student stu=new Student();
                stu.setId(rs.getInt("id"));
                stu.setNo(rs.getString("no"));
                stu.setName(rs.getString("name"));
                stu.setAge(rs.getInt("age"));
                students.add(stu);
            }
            return students;
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }

    }

    /**
     * 根据学号查询指定学生信息
     * @param no
     * @return
     * @throws DataAccessException
     */
    public static Student selectByNo(String no) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Student stu=null;
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where no=? ")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setString(1,no);
            rs=ps.executeQuery();
            while(rs.next()){
                stu=new Student();
                stu.setId(rs.getInt("id"));
                stu.setNo(rs.getString("no"));
                stu.setName(rs.getString("name"));
                stu.setAge(rs.getInt("age"));
            }
            return stu;
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
    }

    public static List<Student> selectByName(String name) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        List<Student> students=new ArrayList<>();
        try {
            conn=JDBCUtil.getConnection();

            // select * from t_student where name like '%/%%' escape '/';
            String sql=new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where name like ? ")
                    .append(" escape '/'")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setString(1,"%/"+name+"%");
            rs=ps.executeQuery();
            while(rs.next()){
                Student stu=new Student();
                stu.setId(rs.getInt("id"));
                stu.setNo(rs.getString("no"));
                stu.setName(rs.getString("name"));
                stu.setAge(rs.getInt("age"));
                students.add(stu);
            }
            return students;
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
    }


    public static List<Student> selectByAge(int minAge,int maxAge) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        List<Student> students=new ArrayList<>();
        try {
            conn=JDBCUtil.getConnection();


            String sql=new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where age between ? and ? ")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setInt(1,minAge);
            ps.setInt(2,maxAge);
            rs=ps.executeQuery();
            while(rs.next()){
                Student stu=new Student();
                stu.setId(rs.getInt("id"));
                stu.setNo(rs.getString("no"));
                stu.setName(rs.getString("name"));
                stu.setAge(rs.getInt("age"));
                students.add(stu);
            }
            return students;
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
    }

}
